﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Practices.ServiceLocation;
using Pap.Data.Abstraction.Utils;
using Pap.Services.Settings;
using ConfigSettings = Pap.Services.Settings;
using System.Collections;

namespace Pap.Data.LToS.Repositories
{
    /// <summary>
    /// 
    /// </summary>
    class AdministrationRepositoryImpl : IAdministrationRepository
    {
        /// <summary>
        /// 
        /// </summary>
        private IDatabase _Database;

        /// <summary>
        /// Initializes a new instance of the <see cref="AdministrationRepositoryImpl"/> class.
        /// </summary>
        /// <param name="database">The database.</param>
        public AdministrationRepositoryImpl(IDatabase database)
        {
            _Database = database;
        }

        #region Villages

        /// <summary>
        /// Gets the village by ID.
        /// </summary>
        /// <param name="VillageID">The village ID.</param>
        /// <returns></returns>
        public IVillage GetVillageByID(Guid VillageID)
        {
            return _Database.Villages.SingleOrDefault(v => v.VillageID == VillageID);
        }

        /// <summary>
        /// Finds the villages.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IVillage> FindVillages(System.Linq.Expressions.Expression<Func<IVillage, bool>> Where)
        {
            return _Database.Villages.Where(Where);
        }

        /// <summary>
        /// Creates the village.
        /// </summary>
        /// <param name="Village">The village.</param>
        public void CreateVillage(IVillage Village)
        {
            _Database.Insert<IVillage>(Village);
        }

        /// <summary>
        /// Deletes the villages.
        /// </summary>
        /// <param name="Where">The where.</param>
        public void DeleteVillages(System.Linq.Expressions.Expression<Func<IVillage, bool>> Where)
        {
            _Database.DeleteAll<IVillage>(_Database.Villages.Where(Where));
        }

        #endregion

        #region Committee

        /// <summary>
        /// Gets the committee by ID.
        /// </summary>
        /// <param name="CommitteeID">The committee ID.</param>
        /// <returns></returns>
        public ICommittee GetCommitteeByID(Guid CommitteeID)
        {
            return this._Database.Committees.SingleOrDefault(C => C.CommitteeID == CommitteeID);
        }

        /// <summary>
        /// Finds the committees.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommittee> FindCommittees(System.Linq.Expressions.Expression<Func<ICommittee, bool>> Where)
        {
            return this._Database.Committees.Where(Where);
        }

        /// <summary>
        /// Creates the committee.
        /// </summary>
        /// <param name="Committee">The committee.</param>
        public void CreateCommittee(ICommittee Committee)
        {
            this._Database.Insert<ICommittee>(Committee);
        }


        /// <summary>
        /// Deletes the committees.
        /// </summary>
        /// <param name="Where">The where.</param>
        public void DeleteCommittees(System.Linq.Expressions.Expression<Func<ICommittee, bool>> Where)
        {
            this._Database.DeleteAll(this._Database.Committees.Where(Where));
        }

        /// <summary>
        /// Deletes the committee villages.
        /// </summary>
        /// <param name="Committee">The committee.</param>
        public void DeleteCommitteeVillages(ICommittee Committee)
        {
            this._Database.DeleteAll<IVillage>(Committee.Villages);
        }

        #endregion

        #region [CommitteesURLs]

        /// <summary>
        /// Finds the committees UR ls.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteesURL> FindCommitteesURLs(System.Linq.Expressions.Expression<Func<ICommitteesURL, bool>> Where)
        {
            return this._Database.CommitteesURL.Where(Where);
        }


        public IEnumerable<ICommitteesURL> GetAllCommitteesURLs()
        {
            return _Database.CommitteesURL.Where(curl => true);
        }


        public void CreateURLToNewCommittees()
        {
            var newComm = _Database.Committees.Where(comm => !_Database.CommitteesURL.Select(CURL => CURL.CommitteeID).Contains(comm.CommitteeID));
            foreach (ICommittee comm in newComm)
            {
                ICommitteesURL url = ServiceLocator.Current.GetInstance<CommitteesURL>();
                url.CommitteeID = comm.CommitteeID;
                url.CommitteeName = comm.CommitteeName;
                url.CommitteeURL = "";
                url.CommitteeURLID = Guid.NewGuid();

                _Database.Insert(url);
            }
            _Database.SubmitChanges();

        }

        /// <summary>
        /// Creates the committee URL.
        /// </summary>
        /// <param name="Committee">The committee URL.</param>
        public void CreateCommitteeURL(ICommitteesURL CommitteeUrl)
        {
            this._Database.Insert<ICommitteesURL>(CommitteeUrl);
        }


        public ICommitteesURL GetCommittesURLByCommiteeID(Guid CommitteeID)
        {
            return this._Database.CommitteesURL.FirstOrDefault(curl => curl.CommitteeID == CommitteeID);
        }

        public void UpdateCommitteesURLs(List<Guid> committeeIDs, List<string> URLs)
        {
            for (int i = 0; i < committeeIDs.Count; i++)
            {
                Guid committeeID = committeeIDs[i];
                ICommitteesURL existingRec = _Database.CommitteesURL.FirstOrDefault(committeesURL => committeesURL.CommitteeID == committeeID);
                if (existingRec != null)
                {
                    if (!URLs[i].IsNullOrEmpty())
                        _Database.CommitteesURL.FirstOrDefault(committeesURL => committeesURL.CommitteeID == committeeID).CommitteeURL = URLs[i];
                    else
                        _Database.CommitteesURL.FirstOrDefault(committeesURL => committeesURL.CommitteeID == committeeID).CommitteeURL = null;
                }
                else
                {
                    CommitteesURL newCommitteeURL = new CommitteesURL();
                    newCommitteeURL.CommitteeURLID = Guid.NewGuid();
                    newCommitteeURL.CommitteeID = committeeID;
                    if (!URLs[i].IsNullOrEmpty())
                        newCommitteeURL.CommitteeURL = URLs[i];
                    else
                        newCommitteeURL.CommitteeURL = null;
                    _Database.Insert(newCommitteeURL);
                }
            }
            
        }


        #endregion

        #region CommitteeBankAccounts

        /// <summary>
        /// Gets the committee bank account by ID.
        /// </summary>
        /// <param name="CommitteeBankAccountID">The committee bank account ID.</param>
        /// <returns></returns>
        public ICommitteeBankAccount GetCommitteeBankAccountByID(Guid CommitteeBankAccountID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the committee bank accounts.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteeBankAccount> FindCommitteeBankAccounts(System.Linq.Expressions.Expression<Func<ICommitteeBankAccount, bool>> Where)
        {
            return _Database.CommitteeBankAccounts.Where(Where);
        }

        /// <summary>
        /// Creates the committee bank account.
        /// </summary>
        /// <param name="CommitteeBankAccount">The committee bank account.</param>
        public void CreateCommitteeBankAccount(ICommitteeBankAccount CommitteeBankAccount)
        {
            _Database.Insert<ICommitteeBankAccount>(CommitteeBankAccount);
        }

        /// <summary>
        /// Deletes the committee bank accounts.
        /// </summary>
        /// <param name="Where">The where.</param>
        public void DeleteCommitteeBankAccounts(System.Linq.Expressions.Expression<Func<ICommitteeBankAccount, bool>> Where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region CommitteeBankAccountDepositeReceipt

        /// <summary>
        /// Gets the committee bank account deposit receipt by ID.
        /// </summary>
        /// <param name="CommitteeBankAccountDepositReceiptID">The committee bank account deposit receipt ID.</param>
        /// <returns></returns>
        public ICommitteeBankAccountDepositReceipt GetCommitteeBankAccountDepositReceiptByID(Guid CommitteeBankAccountDepositReceiptID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the committee bank account deposit receiptes.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteeBankAccountDepositReceipt> FindCommitteeBankAccountDepositReceiptes(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountDepositReceipt, bool>> where)
        {
            return _Database.CommitteeBankAccountDepositReceipts.Where(where);
        }

        /// <summary>
        /// Creates the committee bank account deposit receipt.
        /// </summary>
        /// <param name="CommitteeBankAccountDepositReceipt">The committee bank account deposit receipt.</param>
        public void CreateCommitteeBankAccountDepositReceipt(ICommitteeBankAccountDepositReceipt CommitteeBankAccountDepositReceipt)
        {
            _Database.Insert<ICommitteeBankAccountDepositReceipt>(CommitteeBankAccountDepositReceipt);
        }

        /// <summary>
        /// Deletes the committee bank account deposit receiptes.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteCommitteeBankAccountDepositReceiptes(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountDepositReceipt, bool>> where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region ICommitteeBankAccountDepositAttachment


        /// <summary>
        /// Gets the committee bank account deposit attachment by ID.
        /// </summary>
        /// <param name="committeeBankAccountDepositAttachmentID">The committee bank account deposit attachment ID.</param>
        /// <returns></returns>
        public ICommitteeBankAccountDepositAttachment GetCommitteeBankAccountDepositAttachmentByID(Guid committeeBankAccountDepositAttachmentID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the committee bank account deposit attachments.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteeBankAccountDepositAttachment> FindCommitteeBankAccountDepositAttachments(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountDepositAttachment, bool>> where)
        {
            return _Database.CommitteeBankAccountDepositAttachments.Where(where);
        }

        /// <summary>
        /// Creates the committee bank account deposit attachment.
        /// </summary>
        /// <param name="committeeBankAccountDepositAttachment">The committee bank account deposit attachment.</param>
        public void CreateCommitteeBankAccountDepositAttachment(ICommitteeBankAccountDepositAttachment committeeBankAccountDepositAttachment)
        {
            _Database.Insert<ICommitteeBankAccountDepositAttachment>(committeeBankAccountDepositAttachment);
        }

        /// <summary>
        /// Deletes the committee bank account deposit attachments.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteCommitteeBankAccountDepositAttachments(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountDepositAttachment, bool>> where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region CommitteeBankAccountDeposites

        /// <summary>
        /// Gets the committee bank account deposite by ID.
        /// </summary>
        /// <param name="CommitteeBankAccountDepositeID">The committee bank account deposite ID.</param>
        /// <returns></returns>
        public ICommitteeBankAccountDeposite GetCommitteeBankAccountDepositeByID(Guid CommitteeBankAccountDepositeID)
        {
            return _Database.CommitteeBankAccountDeposites.Single(com => com.CommitteeBankAccountDepositID.Equals(CommitteeBankAccountDepositeID));
        }

        /// <summary>
        /// Finds the committee bank account deposites.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteeBankAccountDeposite> FindCommitteeBankAccountDeposites(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountDeposite, bool>> Where)
        {
            return _Database.CommitteeBankAccountDeposites.Where(Where);
        }

        /// <summary>
        /// Creates the committee bank account deposite.
        /// </summary>
        /// <param name="CommitteeBankAccountDeposite">The committee bank account deposite.</param>
        public void CreateCommitteeBankAccountDeposite(ICommitteeBankAccountDeposite CommitteeBankAccountDeposite)
        {
            _Database.Insert<ICommitteeBankAccountDeposite>(CommitteeBankAccountDeposite);
        }

        /// <summary>
        /// Deletes the committee bank account deposites.
        /// </summary>
        /// <param name="Where">The where.</param>
        public void DeleteCommitteeBankAccountDeposites(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountDeposite, bool>> Where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region CommitteeBankAccountExpenses

        /// <summary>
        /// Gets the committee bank account expense by ID.
        /// </summary>
        /// <param name="committeeBankAccountExpenseID">The committee bank account expense ID.</param>
        /// <returns></returns>
        public ICommitteeBankAccountExpense GetCommitteeBankAccountExpenseByID(Guid committeeBankAccountExpenseID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the committee bank account expenses.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteeBankAccountExpense> FindCommitteeBankAccountExpenses(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountExpense, bool>> where)
        {
            return _Database.CommitteeBankAccountExpenses.Where(where);
        }

        /// <summary>
        /// Creates the committee bank account expense.
        /// </summary>
        /// <param name="committeeBankAccountExpense">The committee bank account expense.</param>
        public void CreateCommitteeBankAccountExpense(ICommitteeBankAccountExpense committeeBankAccountExpense)
        {
            _Database.Insert<ICommitteeBankAccountExpense>(committeeBankAccountExpense);
        }

        /// <summary>
        /// Deletes the committee bank account expenses.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteCommitteeBankAccountExpenses(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountExpense, bool>> where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region CommitteeBankAccountMainBranchDeposites

        /// <summary>
        /// Gets the committee bank account main branch deposite by ID.
        /// </summary>
        /// <param name="committeeBankAccountMainBranchDepositeID">The committee bank account main branch deposite ID.</param>
        /// <returns></returns>
        public ICommitteeBankAccountMainBranchDeposite GetCommitteeBankAccountMainBranchDepositeByID(Guid committeeBankAccountMainBranchDepositeID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the committee bank account main branch deposites.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteeBankAccountMainBranchDeposite> FindCommitteeBankAccountMainBranchDeposites(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountMainBranchDeposite, bool>> where)
        {
            return _Database.CommitteeBankAccountMainBranchDeposites.Where(where);
        }

        /// <summary>
        /// Creates the committee bank account main branch deposite.
        /// </summary>
        /// <param name="committeeBankAccountMainBranchDeposite">The committee bank account main branch deposite.</param>
        public void CreateCommitteeBankAccountMainBranchDeposite(ICommitteeBankAccountMainBranchDeposite committeeBankAccountMainBranchDeposite)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Deletes the committee bank account main branch deposites.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteCommitteeBankAccountMainBranchDeposites(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountMainBranchDeposite, bool>> where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region CommitteeBankAccountMainBranchDepositAttachments

        /// <summary>
        /// Gets the committee bank account main branch deposit attachment by ID.
        /// </summary>
        /// <param name="committeeBankAccountMainBranchDepositAttachmentID">The committee bank account main branch deposit attachment ID.</param>
        /// <returns></returns>
        public ICommitteeBankAccountMainBranchDepositAttachment GetCommitteeBankAccountMainBranchDepositAttachmentByID(Guid committeeBankAccountMainBranchDepositAttachmentID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the committee bank account main branch deposit attachments.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICommitteeBankAccountMainBranchDepositAttachment> FindCommitteeBankAccountMainBranchDepositAttachments(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountMainBranchDepositAttachment, bool>> where)
        {
            return _Database.CommitteeBankAccountMainBranchDepositAttachments.Where(where);
        }

        /// <summary>
        /// Creates the committee bank account main branch deposit attachment.
        /// </summary>
        /// <param name="CommitteeBankAccountMainBranchDepositAttachment">The committee bank account main branch deposit attachment.</param>
        public void CreateCommitteeBankAccountMainBranchDepositAttachment(ICommitteeBankAccountMainBranchDepositAttachment CommitteeBankAccountMainBranchDepositAttachment)
        {
            _Database.Insert(CommitteeBankAccountMainBranchDepositAttachment);
        }

        /// <summary>
        /// Deletes the committee bank account main branch deposit attachments.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteCommitteeBankAccountMainBranchDepositAttachments(System.Linq.Expressions.Expression<Func<ICommitteeBankAccountMainBranchDepositAttachment, bool>> where)
        {
            _Database.DeleteAll<ICommitteeBankAccountMainBranchDepositAttachment>(_Database.CommitteeBankAccountMainBranchDepositAttachments.Where(where));
        }

        #endregion

        #region MainCommitteeDeposits

        /// <summary>
        /// Gets the main committee deposit ID.
        /// </summary>
        /// <param name="MainCommitteeDepositID">The main committee deposit ID.</param>
        /// <returns></returns>
        public IMainCommitteeDeposit GetMainCommitteeDepositID(Guid MainCommitteeDepositID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the main committee deposites.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IMainCommitteeDeposit> FindMainCommitteeDeposites(System.Linq.Expressions.Expression<Func<IMainCommitteeDeposit, bool>> where)
        {
            return _Database.MainCommitteeDeposits.Where(where);
        }

        /// <summary>
        /// Creates the main committee deposite.
        /// </summary>
        /// <param name="mainCommitteeDeposite">The main committee deposite.</param>
        public void CreateMainCommitteeDeposite(IMainCommitteeDeposit mainCommitteeDeposite)
        {
            _Database.Insert<IMainCommitteeDeposit>(mainCommitteeDeposite);
        }

        /// <summary>
        /// Deletes the main committee deposites.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteMainCommitteeDeposites(System.Linq.Expressions.Expression<Func<IMainCommitteeDeposit, bool>> where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Centers


        /// <summary>
        /// Gets the center by ID.
        /// </summary>
        /// <param name="CenterID">The center ID.</param>
        /// <returns></returns>
        public ICenter GetCenterByID(Guid CenterID)
        {
            return this._Database.Centers.SingleOrDefault(c => c.CenterID == CenterID);
        }

        /// <summary>
        /// Finds the centers.
        /// </summary>
        /// <param name="Where">The where.</param>
        /// <returns></returns>
        public IEnumerable<ICenter> FindCenters(System.Linq.Expressions.Expression<Func<ICenter, bool>> Where)
        {
            return this._Database.Centers.Where(Where);
        }

        /// <summary>
        /// Creates the center.
        /// </summary>
        /// <param name="Center">The center.</param>
        public void CreateCenter(ICenter Center)
        {
            this._Database.Insert(Center);
        }

        /// <summary>
        /// Deletes the centers.
        /// </summary>
        /// <param name="Where">The where.</param>
        public void DeleteCenters(System.Linq.Expressions.Expression<Func<ICenter, bool>> Where)
        {
            this._Database.DeleteAll(_Database.Centers.Where(Where));
        }

        #endregion

        #region SettingCategories
        /// <summary>
        /// Gets the configuration section.
        /// </summary>
        /// <returns></returns>
        private SettingCategories GetConfigurationSection()
        {
            SettingCategories section = (SettingCategories)System.Configuration.ConfigurationManager.GetSection("settingCategories");
            return section;
        }

        /// <summary>
        /// Gets the settings for committee.
        /// </summary>
        /// <param name="committeeID">The committee ID.</param>
        /// <returns></returns>
        public IEnumerable<ISettingCategory> GetSettingsForCommittee(Guid committeeID)
        {
            List<ISettingCategory> lstCategories;

            SettingCategories configSection = GetConfigurationSection();
            lstCategories = new List<ISettingCategory>();
            foreach (Category catItem in configSection.Categories)
            {
                ISettingCategory cat = ServiceLocator.Current.GetInstance<ISettingCategory>();
                cat.Name = catItem.Name;
                foreach (ConfigSettings.Setting item in catItem.Settings)
                {
                    ISetting setting = _Database.Settings.Single(s => s.Key == item.DatabaseKey & s.CommitteeID == committeeID);

                    setting.Label = item.Label;
                    setting.FieldValidator = GetInitializedValidatorsList(item.Validators);

                    if (item.Editor != null)
                    {
                        setting.EditorType = item.Editor.Type;
                        setting.EditorParams = GetAttributesDictionary(item.Editor.Params);
                    }

                    cat.Settings.Add(setting);
                }
                lstCategories.Add(cat);
            }

            return lstCategories;
        }

        /// <summary>
        /// Gets the initialized validators list.
        /// </summary>
        /// <param name="vals">The vals.</param>
        /// <returns></returns>
        private List<ValidatorBase> GetInitializedValidatorsList(Validators vals)
        {
            List<ValidatorBase> result = new List<ValidatorBase>();
            foreach (Validator val in vals)
            {
                if (val.Type.IsNullOrEmpty())
                {
                    result.Add((ValidatorBase)Activator.CreateInstance(Type.GetType(val.Type)));
                }
                else
                {
                    Dictionary<string, string> validatorAttributes = GetAttributesDictionary(val.Params);
                    try
                    {
                        result.Add((ValidatorBase)Activator.CreateInstance(Type.GetType(val.Type), validatorAttributes));
                    }
                    catch (TypeLoadException exp)
                    {
                        throw new Exception("Could not initialize validator type " + val.Type, exp);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Gets the attributes dictionary.
        /// </summary>
        /// <param name="strParams">The STR params.</param>
        /// <returns></returns>
        public Dictionary<string, string> GetAttributesDictionary(string strParams)
        {
            Dictionary<string, string> attributesDictionary = new Dictionary<string, string>();
            string[] attributes = strParams.Trim().Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (var attribute in attributes)
            {
                string[] attributeParts = attribute.Trim().Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
                attributesDictionary.Add(attributeParts[0].Trim(), attributeParts[1].Trim());
            }
            return attributesDictionary;
        }

        /// <summary>
        /// Gets the label to setting mapping.
        /// </summary>
        /// <param name="committeeID">The committee ID.</param>
        /// <returns></returns>
        public Dictionary<string, ISetting> GetLabelToSettingMapping(Guid committeeID)
        {
            var categories = GetSettingsForCommittee(committeeID);

            Dictionary<string, ISetting> labelToSettingMap = new Dictionary<string, ISetting>();
            foreach (ISettingCategory catItem in categories)
            {
                foreach (ISetting item in catItem.Settings)
                {
                    labelToSettingMap.Add(item.Label, item);
                }
            }
            return labelToSettingMap;
        }

        /// <summary>
        /// Gets all committees.
        /// </summary>
        /// <returns></returns>
        public IEnumerable<ICommittee> GetAllCommittees()
        {
            return _Database.Committees;
        }

        /// <summary>
        /// Gets all non main committees.
        /// </summary>
        /// <returns></returns>
        public IEnumerable<ICommittee> GetAllNonMainCommittees()
        {
            return _Database.Committees.Where(comm => comm.IsMainCommittee.Value == false);
        }

        /// <summary>
        /// Gets the setting value.
        /// </summary>
        /// <param name="CommitteeID">The committee ID.</param>
        /// <param name="settingKey">The setting key.</param>
        /// <returns></returns>
        public string GetSettingValue(Guid CommitteeID, SettingKeys settingKey)
        {
            string settingVal = (from setting in _Database.Settings
                                 where setting.CommitteeID == CommitteeID && setting.Key == settingKey.ToString()
                                 select setting.Value).SingleOrDefault();
            return settingVal;
        }
        #endregion

        #region Projects

        /// <summary>
        /// Gets the last stage.
        /// </summary>
        /// <returns></returns>
        public IProjectStage GetLastStage()
        {
            IQueryable<IProjectStage> stages = _Database.ProjectStages;
            if (stages.Count() > 0)
            {
                int max = stages.Max(s => s.Stage);
                return stages.SingleOrDefault(stage => stage.Stage == max);
            }
            else return null;
        }

        /// <summary>
        /// Gets the stage by number.
        /// </summary>
        /// <param name="stageNumber">The stage number.</param>
        /// <returns></returns>
        public IProjectStage GetStageByNumber(int stageNumber)
        {
            return _Database.ProjectStages.SingleOrDefault(stage => stage.Stage == stageNumber);
        }

        /// <summary>
        /// Gets the previous stage.
        /// </summary>
        /// <param name="stage">The stage.</param>
        /// <returns></returns>
        public IProjectStage GetPreviousStage(IProjectStage stage)
        {
            return GetStageByNumber(stage.Stage - 1);
        }

        /// <summary>
        /// Gets the next stage.
        /// </summary>
        /// <param name="stage">The stage.</param>
        /// <returns></returns>
        public IProjectStage GetNextStage(IProjectStage stage)
        {
            return GetStageByNumber(stage.Stage + 1);
        }

        /// <summary>
        /// Deletes the stage.
        /// </summary>
        /// <param name="stage">The stage.</param>
        public void DeleteStage(IProjectStage stage)
        {
            this._Database.Delete<IProjectStage>(stage);
        }

        /// <summary>
        /// Gets the project by id.
        /// </summary>
        /// <param name="projectId">The project id.</param>
        /// <returns></returns>
        public IProject GetProjectById(Guid projectId)
        {
            return _Database.Projects.Single(project => project.ProjectID == projectId);
        }

        /// <summary>
        /// Gets the user project.
        /// </summary>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public IEnumerable<IProject> GetUserProjects(Guid userID)
        {
            //return _Database.Users.Single(user => user.UserID == userID);
            //return _Database.Projects.Where(proj => proj.ProjectID == "00000000-0000-0000-1111-000000000000".ToGuid()).First();
            return _Database.Users.Single(user => user.UserID == userID).Projects;
        }

        /// <summary>
        /// Finds the projects.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IProject> FindProjects(System.Linq.Expressions.Expression<Func<IProject, bool>> where)
        {
            return _Database.Projects.Where(where);
        }

        /// <summary>
        /// Creates the project.
        /// </summary>
        /// <param name="project">The project.</param>
        public void CreateProject(IProject project)
        {
            _Database.Insert<IProject>(project);
        }

        /// <summary>
        /// Deletes the project.
        /// </summary>
        /// <param name="project">The project.</param>
        public void DeleteProject(IProject project)
        {
            foreach (var stage in project.Stages)
            {
                _Database.Delete<IProjectStage>(stage);
            }
            _Database.Delete<IProject>(project);
        }

        #endregion

        #region ProjectStages

        /// <summary>
        /// Gets the project stage by ID.
        /// </summary>
        /// <param name="projectStageID">The project stage ID.</param>
        /// <returns></returns>
        public IProjectStage GetProjectStageByID(Guid projectStageID)
        {
            return this._Database.ProjectStages.SingleOrDefault(ps => ps.ProjectStageID == projectStageID);
        }

        /// <summary>
        /// Finds the project stages.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IProjectStage> FindProjectStages(System.Linq.Expressions.Expression<Func<IProjectStage, bool>> where)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Creates the project stage.
        /// </summary>
        /// <param name="projectStage">The project stage.</param>
        public void CreateProjectStage(IProjectStage projectStage)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Deletes the project stages.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteProjectStages(System.Linq.Expressions.Expression<Func<IProjectStage, bool>> where)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region DatabaseManagement

        /// <summary>
        /// Backups the database.
        /// </summary>
        public void BackupDatabase(string path)
        {
            DirectoryInfo backUpDirectory = new DirectoryInfo(Path.GetDirectoryName(path));
            if (backUpDirectory.Exists)
                backUpDirectory.Delete(true);
            backUpDirectory.Create();
            SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(new ConnectionStrings().ConnectionString1);
            string dbName = connBuilder.InitialCatalog;

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("BACKUP DATABASE [{0}] \r", dbName);
            builder.AppendFormat("TO DISK = N'{0}' \r", path);
            builder.AppendFormat("WITH NOFORMAT, NOINIT, NAME = N'{0}-Full Database Backup', \r", dbName);
            builder.AppendLine("SKIP, NOREWIND, NOUNLOAD, STATS = 10");

            connBuilder.InitialCatalog = "Master";
            SqlConnection conn = new SqlConnection(connBuilder.ToString());
            SqlCommand comm = new SqlCommand(builder.ToString(), conn);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Restores the databas.
        /// </summary>
        public void RestoreDatabas(string path)
        {
            SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(new ConnectionStrings().ConnectionString1);
            string dbName = connBuilder.InitialCatalog;
            FileInfo backUpFile = new FileInfo(path);
            if (!backUpFile.Exists)
                throw new Exception(string.Format("Backup file {0} not found.", backUpFile));

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("RESTORE DATABASE [{0}] \r", dbName);
            builder.AppendFormat("FROM DISK = N'{0}' \r", backUpFile);
            builder.AppendLine("WITH  FILE = 1, NOUNLOAD, REPLACE, STATS = 10");


            connBuilder.InitialCatalog = "Master";
            SqlConnection conn = new SqlConnection(connBuilder.ToString());
            SqlCommand closeConnectionsComm = new SqlCommand(string.Format("alter database [{0}] set single_user with rollback immediate", dbName), conn);
            SqlCommand reOpenConnectionsComm = new SqlCommand(string.Format("alter database [{0}] set Multi_user with rollback immediate", dbName), conn);
            SqlCommand comm = new SqlCommand(builder.ToString(), conn);
            try
            {
                conn.Open();
                closeConnectionsComm.ExecuteNonQuery();
                comm.ExecuteNonQuery();
                reOpenConnectionsComm.ExecuteNonQuery();
                conn.Close();
            }
            finally
            {
                conn.Close();
            }
            try
            {
                //do this to refresh the connection to the new database
                _Database.Boards.Count();
            }
            catch
            {
            }
        }

        #endregion



        public long GetNextReceiptNumber(Guid CommitteeID)
        {
            System.Nullable<long> maxReceipt = (from recipt in _Database.Receipts
                                                select recipt.ReceiptNumber).Max();


            if (maxReceipt != null)
                return maxReceipt.Value + 1;

            return long.Parse(GetSettingValue(CommitteeID, SettingKeys.ReceiptSeed));


        }

        #region IAdministrationRepository Members


        /// <summary>
        /// Gets the main committee expense ID.
        /// </summary>
        /// <param name="MainCommitteeExpenseID">The main committee expense ID.</param>
        /// <returns></returns>
        public IMainCommitteeExpense GetMainCommitteeExpenseID(Guid MainCommitteeExpenseID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Finds the main committee expenses.
        /// </summary>
        /// <param name="where">The where.</param>
        /// <returns></returns>
        public IEnumerable<IMainCommitteeExpense> FindMainCommitteeExpenses(System.Linq.Expressions.Expression<Func<IMainCommitteeExpense, bool>> where)
        {
            return _Database.MainCommitteeExpenses.Where(where);
        }

        /// <summary>
        /// Creates the main committee expense.
        /// </summary>
        /// <param name="MainCommitteeExpense">The main committee expense.</param>
        public void CreateMainCommitteeExpense(IMainCommitteeExpense MainCommitteeExpense)
        {
            _Database.Insert<IMainCommitteeExpense>(MainCommitteeExpense);
        }

        /// <summary>
        /// Deletes the main committee expenses.
        /// </summary>
        /// <param name="where">The where.</param>
        public void DeleteMainCommitteeExpenses(System.Linq.Expressions.Expression<Func<IMainCommitteeExpense, bool>> where)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
